﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Links
{
    internal static class LinkDAL
    {
        internal static LinkEntity GetLinkByReader(IDataReader dr)
        {
            LinkEntity link = null;

            link = new LinkEntity();
            link.LinkID = dr.GetInt32(0);
            link.Name = dr.IsDBNull(1) ? null : dr.GetString(1);
            link.Url = dr.IsDBNull(2) ? null : dr.GetString(2);
            link.Detail = dr.IsDBNull(3) ? null : dr.GetString(3);
            link.ImageUrl = dr.IsDBNull(4) ? null : dr.GetString(4);
            link.Sort = dr.IsDBNull(5) ? 0 : dr.GetInt32(5);
            link.Hits = dr.IsDBNull(6) ? 0 : dr.GetInt32(6);
            link.Created = Util.ConverDateTime(dr.GetValue(7));
            link.Type = dr.IsDBNull(8) ? 0 : dr.GetInt32(8);

            return link;
        }

        internal static int GetLinkCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectLink = @"SELECT COUNT([LinkID]) FROM [Link]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLink, ref parameterlst);
            DbCommand SelectLinkCommand = db.GetSqlStringCommand(SelectLink);
            DbHelper.PackDbCommandByDbParameters(SelectLinkCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectLinkCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<LinkEntity> GetLinkList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<LinkEntity> linklst = new List<LinkEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectLink = @"
                SELECT [LinkID],[Name],[Url],[Detail],[ImageUrl],[Sort],[Hits],[Created],[Type]
	            FROM (
                    SELECT [LinkID],[Name],[Url],[Detail],[ImageUrl],[Sort],[Hits],[Created],[Type],
                      ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectLink += " " + sortField + " " + sortMode + ",";
            SelectLink += @" [LinkID] Desc
                ) AS rowNum 
                    FROM [Link] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLink, ref parameterlst);
            SelectLink += @"
                ) AS [Link] 
	            WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectLinkCommand = db.GetSqlStringCommand(SelectLink);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectLinkCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectLinkCommand))
            {
                while (dr.Read())
                {
                    LinkEntity link = GetLinkByReader(dr);

                    linklst.Add(link);
                }
            }

            return linklst;
        }

        internal static List<LinkEntity> GetLinkList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<LinkEntity> linklst = new List<LinkEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectLink = @"
                SELECT [LinkID],[Name],[Url],[Detail],[ImageUrl],[Sort],[Hits],[Created],[Type] 
                FROM [Link]  ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectLink, ref parameterlst);
            SelectLink += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectLink += " " + sortField + " " + sortMode + ",";
            SelectLink += " [Link].[LinkID] Asc ";

            DbCommand SelectLinkCommand = db.GetSqlStringCommand(SelectLink);
            DbHelper.PackDbCommandByDbParameters(SelectLinkCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectLinkCommand))
            {
                while (dr.Read())
                {
                    LinkEntity link = GetLinkByReader(dr);

                    linklst.Add(link);
                }
            }

            return linklst;
        }

        internal static LinkEntity GetLink(int linkID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            LinkEntity link = null;

            string SelectLink = @"
                SELECT [LinkID],[Name],[Url],[Detail],[ImageUrl],[Sort],[Hits],[Created],[Type]
                FROM [Link] 
                WHERE [LinkID]=@LinkID
	           ";

            DbCommand SelectLinkCommand = db.GetSqlStringCommand(SelectLink);
            db.AddInParameter(SelectLinkCommand, "@LinkID", DbType.Int32, linkID);

            using (IDataReader dr = db.ExecuteReader(SelectLinkCommand))
            {
                if (dr.Read())
                {
                    link = GetLinkByReader(dr);
                }
            }

            return link;
        }

        internal static int InsertLink(LinkEntity link)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string InsertLink = @"
            DECLARE @maxSort AS INT;
            SELECT @maxSort=MIN([Sort]) FROM [Link];
            INSERT INTO [Link]
            ([Name],[Url],[Detail],[ImageUrl],[Sort],[Hits],[Type])
             VALUES
            (@Name,@Url,@Detail,@ImageUrl,ISNULL(@maxSort,0)+1,0,@Type)";

            DbCommand InsertLinkCommand = db.GetSqlStringCommand(InsertLink);
            db.AddInParameter(InsertLinkCommand, "@Name", DbType.String, link.Name);
            db.AddInParameter(InsertLinkCommand, "@Url", DbType.String, link.Url);
            db.AddInParameter(InsertLinkCommand, "@Detail", DbType.String, link.Detail);
            db.AddInParameter(InsertLinkCommand, "@ImageUrl", DbType.String, link.ImageUrl);
            db.AddInParameter(InsertLinkCommand, "@Type", DbType.Int32, link.Type);
            result = db.ExecuteNonQuery(InsertLinkCommand);

            return result;
        }

        internal static int UpdateLink(LinkEntity link)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();
            string UpdateLink = @"
            UPDATE [Link]
            SET [Name]=@Name,[Url]=@Url,[Detail]=@Detail,[ImageUrl]=@ImageUrl,
                [Sort]=@Sort,[Hits]=@Hits,[Type]=@Type
            WHERE [LinkID] = @LinkID";

            DbCommand UpdateLinkCommand = db.GetSqlStringCommand(UpdateLink);
            db.AddInParameter(UpdateLinkCommand, "@Name", DbType.String, link.Name);
            db.AddInParameter(UpdateLinkCommand, "@Url", DbType.String, link.Url);
            db.AddInParameter(UpdateLinkCommand, "@Detail", DbType.String, link.Detail);
            db.AddInParameter(UpdateLinkCommand, "@ImageUrl", DbType.String, link.ImageUrl);
            db.AddInParameter(UpdateLinkCommand, "@Sort", DbType.Int32, link.Sort);
            db.AddInParameter(UpdateLinkCommand, "@Hits", DbType.Int32, link.Hits);
            db.AddInParameter(UpdateLinkCommand, "@Type", DbType.Int32, link.Type);
            db.AddInParameter(UpdateLinkCommand, "@LinkID", DbType.Int32, link.LinkID);
            result = db.ExecuteNonQuery(UpdateLinkCommand);

            return result;
        }

        internal static int DeleteLink(int linkID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteLink = @"
            DELETE FROM [Link] WHERE LinkID=@LinkID";

            DbCommand DeleteLinkCommand = db.GetSqlStringCommand(DeleteLink);
            db.AddInParameter(DeleteLinkCommand, "@LinkID", DbType.Int32, linkID);
            result = db.ExecuteNonQuery(DeleteLinkCommand);

            return result;
        }
    }
}
